package com.teamit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.teamit.entity.Employee;
import com.teamit.entity.Idea;
import com.teamit.util.db.DBHelp;
import com.teamit.util.db.Page;
import com.teamit.util.db.RowMapper;

public class IdeaDao {
	
	DBHelp<Idea> db = new DBHelp<Idea>();
	
	public void save(Idea idea){
		String sql = "insert into t_idea(id,title,content,createtime,employee_id,project_id) values(?,?,?,?,?,?)";
		db.executeSQL(sql, idea.getId(),idea.getTitle(),idea.getContent(),idea.getCreatetime(),idea.getEmployeeid(),idea.getProjectid());
	}
	public Idea findById(String id) {

		String sql = "select id,title,content,createtime,employee_id,project_id where id=?";
		return db.executeQueryToObject(sql, new IdeaRowMapper(), id);
	}
	public Idea findIdeaById(String id){
		String sql = "select ti.id,ti.title,ti.content,ti.createtime,ti.employee_id,ti.project_id,te.username from t_idea as ti left join t_employee as te on ti.employee_id = te.id where ti.id=?";
		return db.executeQueryToObject(sql, new RowMapper<Idea>(){
			public Idea mapRow(ResultSet rs) throws SQLException {
				
				Idea idea = new Idea();
				idea.setId(rs.getString("id"));
				idea.setTitle(rs.getString("title"));
				idea.setContent(rs.getString("content"));
				idea.setCreatetime(rs.getString("createtime"));
				idea.setProjectid(rs.getString("project_id"));
				idea.setEmployeeid(rs.getString("employee_id")); 
				
				Employee employee = new Employee();
				employee.setName(rs.getString("username"));
				
				idea.setEmployee(employee);
				return idea;
			}
			
		},id);
	}
	public void update(Idea idea) {

		String sql = "update t_idea set title=?,content=? where id=?";
		db.executeSQL(sql, idea.getTitle(),idea.getContent(),idea.getId());
	}
	
	private class IdeaRowMapper implements RowMapper<Idea>{

		public Idea mapRow(ResultSet rs) throws SQLException {

			Idea idea = new Idea();
			idea.setId(rs.getString("id"));
			idea.setTitle(rs.getString("title"));
			idea.setContent(rs.getString("content"));
			idea.setCreatetime(rs.getString("createtime"));
			idea.setProjectid(rs.getString("project_id"));
			idea.setEmployeeid(rs.getString("employee_id")); 
			
			return idea;
		}
		
	}
	
	public void delById(String id){
		String sql="delete from t_idea where id=?";
		db.executeSQL(sql, id);
	}
	public int count() {
		String sql = "select count(*) from t_idea";
		return db.executeQuery(sql).intValue();
	}
	public Page<Idea> findByPage(int pageNum,String Projectid){
	
		Page<Idea> page = new Page<Idea>(count(), pageNum);
		String sql = "select ti.id,ti.title,ti.content,ti.createtime,ti.employee_id,ti.project_id, username from t_idea as ti left join t_employee as te on ti.employee_id = te.id where ti.project_id=? limit ?,?";
		
		List<Idea> list = db.executeQueryToList(sql, new RowMapper<Idea>(){
			public Idea mapRow(ResultSet rs) throws SQLException {
				
				Idea idea = new Idea();
				idea.setId(rs.getString("id"));
				idea.setTitle(rs.getString("title"));
				idea.setContent(rs.getString("content"));
				idea.setCreatetime(rs.getString("createtime"));
				idea.setProjectid(rs.getString("project_id"));
				idea.setEmployeeid(rs.getString("employee_id")); 
				
				Employee employee = new Employee();
				employee.setName(rs.getString("username"));
				
				idea.setEmployee(employee);
				return idea;
			}
			
		},Projectid, page.getStart(),page.getPageSize());
		
		page.setList(list);
		return page;
	}
	

}
